﻿using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;


namespace DAL
{
    public class HoaDonBanHangDAL
    {
        private SqlConnection cn = null;
        public HoaDonBanHangDAL()
        {
            string stringConnection = ConfigurationSettings.AppSettings["connectionstring"].ToString();
            cn = new SqlConnection(stringConnection);
            cn.Open();
        }

        public void INSERT(HoaDonBanHang D)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandText = "sp_HOADONBANHANG_Insert";
            cm.Connection = this.cn;
            cm.CommandType = CommandType.StoredProcedure;

            cm.Parameters.AddWithValue("@TenDangNhap_NguoiMua", D.TenDangNhap_NguoiMua);
            cm.Parameters.AddWithValue("@TenDangNhap_NhanVien", D.TenDangNhap_NhanVien);
            cm.Parameters.AddWithValue("@NgayDat", D.NgayDat);
            cm.Parameters.AddWithValue("@NgayGiao", D.NgayGiao);
            cm.Parameters.AddWithValue("@ThanhTien", D.ThanhTien);
            cm.Parameters.AddWithValue("@DiaChiGiaoHang", D.DiaChiGiaoHang);
            cm.Parameters.AddWithValue("@MaTinhTrangHoaDon", D.MaTinhTrangHoaDon);

            cm.ExecuteNonQuery();
        }

        public void UPDATE(HoaDonBanHang D)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandText = "sp_HOADONBANHANG_Update";
            cm.Connection = this.cn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@MaHoaDonBanHang", D.MaHoaDonBanHang);
            cm.Parameters.AddWithValue("@TenDangNhap_NguoiMua", D.TenDangNhap_NguoiMua);
            cm.Parameters.AddWithValue("@TenDangNhap_NhanVien", D.TenDangNhap_NhanVien);
            cm.Parameters.AddWithValue("@NgayDat", D.NgayDat);
            cm.Parameters.AddWithValue("@NgayGiao", D.NgayGiao);
            cm.Parameters.AddWithValue("@ThanhTien", D.ThanhTien);
            cm.Parameters.AddWithValue("@DiaChiGiaoHang", D.DiaChiGiaoHang);
            cm.Parameters.AddWithValue("@MaTinhTrangHoaDon", D.MaTinhTrangHoaDon);
            cm.ExecuteNonQuery();
        }
        public void UPDATE_TrangThai(int mahoadon, int trangthai)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandText = "sp_HOADONBANHANG_UpdateTrangThai";
            cm.Connection = this.cn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@MaHoaDonBanHang", mahoadon);
            cm.Parameters.AddWithValue("@MaTinhTrangHoaDon", trangthai);
            cm.ExecuteNonQuery();
        }
        public void DELETE(int iD)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandText = "MaHoaDonBanHang";
            cm.Connection = this.cn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@MaHoaDonBanHang", iD);
            cm.ExecuteNonQuery();
        }

        public int MaHoaDonBanHangCuoiCung(string tendangnhap_nguoimua)
        {
            SqlCommand laymasp = new SqlCommand("select MaHoaDonBanHang from HOADONBANHANG where TenDangNhap_NguoiMua=@tendangnhap and MaHoaDonBanHang = (select MAX(MaHoaDonBanHang) from HOADONBANHANG)", cn);
            laymasp.Parameters.AddWithValue("@tendangnhap", tendangnhap_nguoimua);
            return int.Parse(laymasp.ExecuteScalar().ToString());
        }
        public HoaDonBanHangCollection GET(string Where, int PageIndex, int PageSize, string OrderBy, string OrderDirection, out int TotalRecords)
        {
            SqlCommand cm = new SqlCommand();
            cm.CommandText = "sp_HOADONBANHANG_Get";
            cm.Connection = this.cn;
            cm.CommandType = CommandType.StoredProcedure;
            cm.Parameters.AddWithValue("@Where", Where);
            cm.Parameters.AddWithValue("@PageIndex", PageIndex);
            cm.Parameters.AddWithValue("@PageSize", PageSize);
            cm.Parameters.AddWithValue("@OrderBy", OrderBy);
            cm.Parameters.AddWithValue("@OrderDirection", OrderDirection);
            SqlParameter param = new SqlParameter("@TotalRecords", DbType.Int32);
            param.Direction = ParameterDirection.Output;
            cm.Parameters.Add(param);
            SqlDataAdapter da = new SqlDataAdapter(cm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            HoaDonBanHangCollection HoaDonBanHangCollection = new HoaDonBanHangCollection();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                HoaDonBanHang D = new HoaDonBanHang();
                D.MaHoaDonBanHang = int.Parse(ds.Tables[0].Rows[i]["MaHoaDonBanHang"].ToString());
                D.TenDangNhap_NguoiMua = ds.Tables[0].Rows[i]["TenDangNhap_NguoiMua"].ToString();
                D.TenDangNhap_NhanVien = ds.Tables[0].Rows[i]["TenDangNhap_NhanVien"].ToString();
                D.NgayDat = DateTime.Parse(ds.Tables[0].Rows[i]["NgayDat"].ToString());
                D.NgayGiao = DateTime.Parse(ds.Tables[0].Rows[i]["NgayGiao"].ToString());
                D.ThanhTien = int.Parse(ds.Tables[0].Rows[i]["ThanhTien"].ToString());
                D.DiaChiGiaoHang = ds.Tables[0].Rows[i]["DiaChiGiaoHang"].ToString();
                D.MaTinhTrangHoaDon = int.Parse(ds.Tables[0].Rows[i]["MaTinhTrangHoaDon"].ToString());

                HoaDonBanHangCollection.Add(D);
            }
            TotalRecords = int.Parse(cm.Parameters["@TotalRecords"].Value.ToString());
            return HoaDonBanHangCollection;
        }
    }
}
